package hello.app.model;

import hello.app.model.data.Comment;
import hello.app.model.data.HelloContact;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataSource extends SQLiteOpenHelper{

	// データベース名の定数
    private static final String DB_NAME = "hello";
	private static DataSource instance = null;

	private DataSource(Context context) {
		super(context, DB_NAME, null, 9);
		// TODO 自動生成されたコンストラクター・スタブ
	}

	static DataSource getInstance(Context context){
		if(instance  == null){
			instance = new DataSource(context);
		}
		return instance;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();
		try{
            // テーブルの生成
            StringBuilder createSql = new StringBuilder();
            createSql.append("create table " + HelloContact.TABLE_NAME + " (");
            createSql.append(HelloContact.ID + " integer primary key autoincrement,");
            createSql.append(HelloContact.NAME + " text,");
            createSql.append(HelloContact.PHONE + " text,");
            createSql.append(HelloContact.GOOD + " integer,");
            createSql.append(HelloContact.BAD + " integer,");
            createSql.append(HelloContact.EVALUATED + " integer,");
            createSql.append(HelloContact.ADDRESS + " text,");
            createSql.append(HelloContact.TYPE + " text");
            createSql.append(" )");

            db.execSQL(createSql.toString());

            // テーブルの生成
            createSql = new StringBuilder();
            createSql.append("create table " + Comment.TABLE_NAME + " (");
            createSql.append(Comment.ID + " integer primary key,");
            createSql.append(Comment.CONTACT_ID + " integer,");
            createSql.append(Comment.USER_ID + " integer,");
            createSql.append(Comment.USER_EMAIL + " text,");
            createSql.append(Comment.TEXT + " text,");
            createSql.append(Comment.CREATED_AT + " text,");
            createSql.append(Comment.UPDATED_AT + " text,");
            createSql.append(Comment.GOOD + " integer,");
            createSql.append(Comment.BAD + " integer");
            createSql.append(" )");

            db.execSQL(createSql.toString());
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.beginTransaction();
		try{
			if(oldVersion <= 3){
				// テーブルの生成
				StringBuilder alterSql = new StringBuilder();
	            alterSql.append("ALTER TABLE " + HelloContact.TABLE_NAME +" ");
	            alterSql.append("ADD COLUMN " + HelloContact.ADDRESS +" text");
	            db.execSQL(alterSql.toString());

	            alterSql = new StringBuilder();
	            alterSql.append("ALTER TABLE " + HelloContact.TABLE_NAME +" ");
	            alterSql.append("ADD COLUMN " + HelloContact.TYPE +" text");

	            db.execSQL(alterSql.toString());
			}else if(oldVersion != 7 && newVersion <= 8){
				 // テーブルの生成
	            StringBuilder createSql = new StringBuilder();
	            createSql.append("create table " + Comment.TABLE_NAME + " (");
	            createSql.append(Comment.ID + " integer primary key,");
	            createSql.append(Comment.CONTACT_ID + " integer,");
	            createSql.append(Comment.USER_ID + " integer,");
	            createSql.append(Comment.USER_EMAIL + " text,");
	            createSql.append(Comment.TEXT + " text,");
	            createSql.append(Comment.CREATED_AT + " text,");
	            createSql.append(Comment.UPDATED_AT + " text");
	            createSql.append(" )");

	            db.execSQL(createSql.toString());
			}
			if(newVersion == 8){
				// テーブルの生成
				StringBuilder alterSql = new StringBuilder();
	            alterSql.append("ALTER TABLE " + Comment.TABLE_NAME +" ");
	            alterSql.append("ADD COLUMN " + Comment.GOOD +" integer");
	            db.execSQL(alterSql.toString());

	            alterSql = new StringBuilder();
	            alterSql.append("ALTER TABLE " + Comment.TABLE_NAME +" ");
	            alterSql.append("ADD COLUMN " + Comment.BAD +" integer");

	            db.execSQL(alterSql.toString());
			}
			if(newVersion == 9){
				// テーブルの生成
	            StringBuilder createSql = new StringBuilder();
	            createSql.append("create table " + HelloContact.TABLE_NAME + " (");
	            createSql.append(HelloContact.ID + " integer primary key autoincrement,");
	            createSql.append(HelloContact.NAME + " text,");
	            createSql.append(HelloContact.PHONE + " text,");
	            createSql.append(HelloContact.GOOD + " integer,");
	            createSql.append(HelloContact.BAD + " integer,");
	            createSql.append(HelloContact.EVALUATED + " integer,");
	            createSql.append(HelloContact.ADDRESS + " text,");
	            createSql.append(HelloContact.TYPE + " text");
	            createSql.append(" )");

	            db.execSQL(createSql.toString());
			}
            db.setTransactionSuccessful();
	    } finally {
	        db.endTransaction();
	    }
	}

}
